<?php
namespace app\student\controller;
use app\common\Base;
use think\Db;

class StudentTotal extends Base {
	
	public function index() {
		return $this->fetch();
	}

	public function pageQuery() {
		$where = "";
		$grade = input("grade");
		$page = input("page");
		$rows = input("rows");
		$termKey = intval(currTerm()["termKey"]);
		$where = "转出学期ID = 0 and a.年级号 > ".currTerm()["finishedKey"];
		if($grade) {
			$array = explode("_", $grade);
			$where = $where." and a.年级号 = ".$array[0]." and a.学籍类型 = ".$array[1];
		}
		$sql = "select a.学籍类型,a.年级号,年级名称,a.班号,count(a.ID) as 人数,班主任 
			from 学生信息 a 
			inner join 年级名称 b on a.年级号 = (".$termKey."-b.ID+(case when b.ID <= 3 then 1 else 4 end)) and a.学籍类型 = (case when b.ID <= 3 then 1 else 2 end)
			left join (
				select 班主任科任.年级号,班号,教师信息.学籍类型,姓名 as 班主任 
				from 班主任科任 
				inner join 教师信息 on 班主任科任.班主任ID=教师信息.ID
			) c on a.年级号=c.年级号 and a.班号=c.班号 and a.学籍类型 = c.学籍类型
			where ".$where."
			group by a.学籍类型,a.年级号,年级名称,a.班号,班主任
			UNION ALL 
			select NULL,'9999','人数合计',NULL,count(ID),'---' from 学生信息 a
			where ".$where."
			order by a.年级号 desc,班号";
		$result = Db::query($sql);
		return selectResult($result);
	}

}
